﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FJERP.Model;
using System.Data.SqlClient;
using System.Data;

namespace FJERP.DAL
{
   public  class ProductDAL
    {
        SQLHelper helper = new SQLHelper();
        /// <summary>
        /// 商品列表显示
        /// </summary>
        /// <returns></returns>
        public List<Product> Product_show(int PageSize, int PageIndex)
        {
            string sql = "select Product_Id, Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from(select ROW_NUMBER()over(order by Product_Id)as   nid,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product.Product_Type_Id,Product_Id,Product_Unit_Id,Product_Remark from Product INNER JOIN  Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id )as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
            DataTable table = helper.ExecDataTable(sql);
            List<Product> list = new List<Product>();
            foreach (DataRow item in table.Rows)
            {
                Product P = new Product();
                P.Product_Id = Convert.ToInt32(item["Product_Id"]);
                P.Product_Barcode = item["Product_Barcode"].ToString();
                P.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                P.Product_frim = item["Product_frim"].ToString();
                P.Product_Modelspe = item["Product_Modelspe"].ToString();
                P.Product_Name = item["Product_Name"].ToString();
                P.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                P.Product_Remark = item["Product_Remark"].ToString();
                P.Product_Type_Name = item["Product_Type_Name"].ToString();
                P.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                list.Add(P);
            }
            return list;
        }
        /// <summary>
        /// 商品总数量
        /// </summary>
        /// <returns></returns>
        public int Product_Count()
        {
            string sql = "select COUNT(*) from Product";
            return Convert.ToInt32(helper.ExecScalar(sql));
        }
        /// <summary>
        /// 删除供应商
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del(int id)
        {
            string sql = "delete Product where Product_Id=" + id + "";
            return helper.ExecNonquery(sql);
        }
        /// <summary>
        /// 根据Id的获取数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable Product_Hu(int id)
        {
            string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark,Product_Type_Id from Product where Product_Id=" + id + "";
            return helper.ExecDataTable(sql);

        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int UptProduct(Product s)
        {
            string ProcName = "UptProduct";
            SqlParameter[] par = new SqlParameter[]
            {
                            new SqlParameter("@Product_Id",s.Product_Id),
                            new SqlParameter("@Product_Name",s.Product_Name),
                            new SqlParameter("@Product_Modelspe",s.Product_Modelspe),
                            new SqlParameter("@Product_Bprid",s.Product_Bprid),
                            new SqlParameter("@Product_Price",s.Product_Price),
                            new SqlParameter("@Product_Type_Id",s.Product_Type_Id),
                            new SqlParameter("@Product_Barcode",s.Product_Barcode),
                            new SqlParameter("@Product_frim",s.Product_frim),
                            new SqlParameter("@Product_Unit_Id",s.Product_Unit_Id),
                            new SqlParameter("@Product_Remark",s.Product_Remark),
                };
            return helper.ExecNonquery_Proc(ProcName, par);
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int AddProduct(Product s)
        {
            string ProcName = "AddSProduct";
            SqlParameter[] par = new SqlParameter[]
            {
                            new SqlParameter("@Product_Name",s.Product_Name),
                            new SqlParameter("@Product_Modelspe",s.Product_Modelspe),
                            new SqlParameter("@Product_Bprid",s.Product_Bprid),
                            new SqlParameter("@Product_Price",s.Product_Price),
                            new SqlParameter("@Product_Type_Id",s.Product_Type_Id),
                            new SqlParameter("@Product_Barcode",s.Product_Barcode),
                            new SqlParameter("@Product_frim",s.Product_frim),
                            new SqlParameter("@Product_Unit_Id",s.Product_Unit_Id),
                            new SqlParameter("@Product_Remark",s.Product_Remark),
                };
            return helper.ExecNonquery_Proc(ProcName, par);
        }
        /// <summary>
        /// 类型显示
        /// </summary>
        /// <returns></returns>
        public DataTable Product_Type_Show()
        {
            string sql = "select Product_Type_Id,Product_Type_Name from Product_Type";
            return helper.ExecDataTable(sql);
        }
        /// <summary>
        /// 商品列表显示查询
        /// </summary>
        /// <returns></returns>
        public List<Product> Product_show_Sel(int PageSize, int PageIndex, string Product_Name, string Product_Modelspe, int? Product_Type_Id)
        {
            string sql = "select Product_Id, Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark from(select ROW_NUMBER()over(order by Product_Id)as   nid,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Type_Name,Product_Barcode,Product_frim,Product.Product_Type_Id,Product_Id,Product_Unit_Id,Product_Remark from Product INNER JOIN  Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id )as temp where nid between (" + PageIndex + "-1)*" + PageSize + "+1 and " + PageIndex + "*" + PageSize + "";
            if (!string.IsNullOrEmpty(Product_Name))
            {
                sql += "and Product_Name like '%" + Product_Name + "%'";
            }
            if (!string.IsNullOrEmpty(Product_Modelspe))
            {
                sql += "and Product_Modelspe like'%" + Product_Modelspe + "%'";
            }
            if (Product_Type_Id != 0)
            {
                sql += "and Product_Type_Id=" + Product_Type_Id + "";
            }
            DataTable table = helper.ExecDataTable(sql);
            List<Product> list = new List<Product>();
            foreach (DataRow item in table.Rows)
            {
                Product P = new Product();
                P.Product_Id = Convert.ToInt32(item["Product_Id"]);
                P.Product_Barcode = item["Product_Barcode"].ToString();
                P.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                P.Product_frim = item["Product_frim"].ToString();
                P.Product_Modelspe = item["Product_Modelspe"].ToString();
                P.Product_Name = item["Product_Name"].ToString();
                P.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                P.Product_Remark = item["Product_Remark"].ToString();
                P.Product_Type_Name = item["Product_Type_Name"].ToString();
                P.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                list.Add(P);
            }
            return list;
        }
        public static object _lock = new object();
        public static int count = 1;
        public string GetRandom1()
        {
            lock (_lock)
            {
                if (count >= 10000)
                {
                    count = 1;
                }
                var number = "P" + DateTime.Now.ToString("yyMMddHHmmss") + count.ToString("0000");
                count++;
                return number;
            }
        }
        public string GetRandom2()
        {
            lock (_lock)
            {
                return "T" + DateTime.Now.Ticks;

            }
        }

        public string GetRandom3()
        {
            lock (_lock)
            {
                Random ran = new Random();
                return "U" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ran.Next(1000, 9999).ToString();
            }
        }
             
    }
}
